﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;

namespace DAO
{
    public class DAO_Comment
    {
        private DataProvider dp;
        public DAO_Comment()
        {
            dp = new DataProvider();
        }
        public List<DTO_Comment1> LayDanhSach()
        {
            List<DTO_Comment1> list = new List<DTO_Comment1>();
            string query = "SELECT COMMENT.NoiDung,COMMENT.TinhTrang, COMMENT.ThoiGian, TAIKHOAN.HoTen, KHUYENMAI.TenKM,KHUYENMAI.MaKM, COMMENT.MaComment FROM COMMENT INNER JOIN TAIKHOAN ON COMMENT.NguoiPost = TAIKHOAN.MaTK INNER JOIN KHUYENMAI ON COMMENT.KhuyenMai = KHUYENMAI.MaKM";
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_Comment1 dto = new DTO_Comment1();
                dto.MaComment = Convert.ToInt32(dr["MaComment"]);
                dto.NguoiPost = dr["HoTen"].ToString();
                dto.MaKhuyenMai = Convert.ToInt32(dr["MaKM"]);
                dto.KhuyenMai = dr["TenKM"].ToString();
                dto.NoiDung = dr["NoiDung"].ToString();
                dto.ThoiGian = Convert.ToDateTime(dr["ThoiGian"]);
                dto.TinhTrang = Convert.ToInt32(dr["TinhTrang"]);
                list.Add(dto);
            }

            return list;
        }
        public List<DTO_Comment1> TimKiemTheoTen(string ten)
        {
            List<DTO_Comment1> list = new List<DTO_Comment1>();
            string query = "SELECT COMMENT.NoiDung,COMMENT.TinhTrang, COMMENT.ThoiGian, TAIKHOAN.HoTen, KHUYENMAI.TenKM,KHUYENMAI.MaKM, COMMENT.MaComment FROM COMMENT INNER JOIN TAIKHOAN ON COMMENT.NguoiPost = TAIKHOAN.MaTK INNER JOIN KHUYENMAI ON COMMENT.KhuyenMai = KHUYENMAI.MaKM And TAIKHOAN.HoTen=N'" + ten + "'";
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_Comment1 dto = new DTO_Comment1();
                dto.MaComment = Convert.ToInt32(dr["MaComment"]);
                dto.MaKhuyenMai = Convert.ToInt32(dr["MaKM"]);
                dto.NguoiPost = dr["HoTen"].ToString();
                dto.KhuyenMai = dr["TenKM"].ToString();
                dto.NoiDung = dr["NoiDung"].ToString();
                dto.ThoiGian = Convert.ToDateTime(dr["ThoiGian"]);
                dto.TinhTrang = Convert.ToInt32(dr["TinhTrang"]);
                list.Add(dto);
            }

            return list;
        }
        public List<DTO_Comment1> TimKiemCMTheoKM(string KM)
        {
            List<DTO_Comment1> list = new List<DTO_Comment1>();
            string query = "SELECT tk.HoTen, cm.NoiDung, cm.ThoiGian FROM COMMENT cm, TAIKHOAN tk where cm.NguoiPost = tk.MaTK and cm.KhuyenMai = " + KM;
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_Comment1 dto = new DTO_Comment1();
                dto.NguoiPost = dr[0].ToString();
                dto.NoiDung = dr[1].ToString(); ;
                dto.ThoiGian = DateTime.Parse(dr[2].ToString());
                list.Add(dto);
            }

            return list;
        }
        public List<DTO_Comment1> TimKiemTheoKM(string KM)
        {
            List<DTO_Comment1> list = new List<DTO_Comment1>();
            string query = "SELECT COMMENT.NoiDung,COMMENT.TinhTrang, COMMENT.ThoiGian, TAIKHOAN.HoTen, KHUYENMAI.TenKM,KHUYENMAI.MaKM, COMMENT.MaComment FROM COMMENT INNER JOIN TAIKHOAN ON COMMENT.NguoiPost = TAIKHOAN.MaTK INNER JOIN KHUYENMAI ON COMMENT.KhuyenMai = KHUYENMAI.MaKM And KHUYENMAI.TenKM=N'" + KM + "'";
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_Comment1 dto = new DTO_Comment1();
                dto.MaComment = Convert.ToInt32(dr["MaComment"]);
                dto.MaKhuyenMai = Convert.ToInt32(dr["MaKM"]);
                dto.NguoiPost = dr["HoTen"].ToString();
                dto.KhuyenMai = dr["TenKM"].ToString();
                dto.NoiDung = dr["NoiDung"].ToString();
                dto.ThoiGian = Convert.ToDateTime(dr["ThoiGian"]);
                dto.TinhTrang = Convert.ToInt32(dr["TinhTrang"]);
                list.Add(dto);
            }

            return list;
        }
        public List<DTO_Comment1> TimKiemTheoMaCM(int MaCM)
        {
            List<DTO_Comment1> list = new List<DTO_Comment1>();
            string query = "SELECT COMMENT.NoiDung,COMMENT.TinhTrang, COMMENT.ThoiGian, TAIKHOAN.HoTen, KHUYENMAI.TenKM,KHUYENMAI.MaKM, COMMENT.MaComment FROM COMMENT INNER JOIN TAIKHOAN ON COMMENT.NguoiPost = TAIKHOAN.MaTK INNER JOIN KHUYENMAI ON COMMENT.KhuyenMai = KHUYENMAI.MaKM And COMMENT.MaComment=" + MaCM.ToString();
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_Comment1 dto = new DTO_Comment1();
                dto.MaComment = Convert.ToInt32(dr["MaComment"]);
                dto.MaKhuyenMai = Convert.ToInt32(dr["MaKM"]);
                dto.NguoiPost = dr["HoTen"].ToString();
                dto.KhuyenMai = dr["TenKM"].ToString();
                dto.NoiDung = dr["NoiDung"].ToString();
                dto.ThoiGian = Convert.ToDateTime(dr["ThoiGian"]);
                dto.TinhTrang = Convert.ToInt32(dr["TinhTrang"]);
                list.Add(dto);
            }

            return list;
        }
        public bool Update(int MaCM, string text)
        {
            string query = "Update COMMENT set NoiDung=N'" + text + "' where MaComment=" + MaCM.ToString();
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
        public bool Delete(int MaCM)
        {
            string query = "Update COMMENT set TinhTrang=2 where MaComment=" + MaCM.ToString();
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
        public bool insertCM(DTO_Comment dto)
        {
            List<DTO_Comment1> list = LayDanhSach();
            int c = list.Count;
            string query = "INSERT INTO COMMENT VALUES(" 
                     + dto.NguoiPost.ToString() + "," + dto.KhuyenMai.ToString() + ",N'" + dto.NoiDung + "','" + dto.ThoiGian.ToString() + "',0)";
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
    }

}
